#!/bin/bash
. /root/.bashrc
source ~/.bash_profile
source /etc/profile

export ORACLE_HOME=/home/hadoop/oracleclient/instantclient_11_2
export PATH=/home/hadoop/oracleclient/instantclient_11_2:$PATH
export ORACLE_OWNER=oracle
export ORACLE_SID=bsmp
export ORACLE_TERM=xterm
export THREADS_FLAG=native
export LD_LIBRARY_PATH=/home/hadoop/oracleclient/instantclient_11_2
export TNS_ADMIN=/home/hadoop/oracleclient/instantclient_11_2

run_home=/home/bsmp/work
workpath=${run_home}/bin/sds_v3
datapath=${run_home}/srcdata
logpath=${run_home}/log
logfile=${logpath}/sitemonitor_storage.log
database=sds/h16aug8v3w@dev111
#database=sds/h16aug8v3w@BSMPSB_GD

echo  $c_time--"sds_idc_sitemonitor_storage begin" >>${logfile}
yesterday=`date +"%Y%m%d" --date="-1 day"`
monthday=`date +"%Y%m"`01

cd ${datapath}
#dos2unix *.txt

#filelist=`ls *.txt -l | grep ${yesterday} | awk '{print $9}'`
#currentfileno=`ls -l | grep txt | grep ${yesterday} | wc -l`

pwd >>${logfile}

echo  `date` --" sds_idc_sitemonitor_storage Start" >>${logfile}

sqlplus -s ${database} << EOFa

delete SDS_IDC_SITEMONITOR where stattime=${yesterday};
delete SDS_IDC_SITEMONITOR_M where reporttime=${yesterday};
COMMIT;

Insert into SDS_IDC_SITEMONITOR(STATTIME,COMPANY,COMPANYTYPE,DOMAIN,HIGH,MIDDLE,LOW,PV,IPNUMBER,STAGE,CC,DDOS,SESSIONMIDDLE,RECORDSTAT,ACCESSAREA,CONTENT,CITYNAMES)
select ${yesterday},company,companytype,ta.domainname,HIGH,MIDDLE,LOW,urlpv,ipcnt,stage,CC,DDOS,SESSIONMIDDLE,RECORDSTAT,ACCESSAREA,KEYWORDS,CITYNAMES from(
select ${yesterday},              
     company,
     domainname,
     companytype,
     count(a.ip) ipcnt,
     decode(webrecordnum,'','0','1') stage,     
     sum(case TYPENAME when 'CC' then TYPEVALUE else 0 end) CC,
     sum(case TYPENAME when 'DDOS' then TYPEVALUE else 0 end) DDOS,
     sum(case TYPENAME when 'SESSIONMIDDLE' then TYPEVALUE else 0 end) SESSIONMIDDLE,     
     wm_concat(distinct IPAREA) CITYNAMES,
     count(case when IPISRECORD=1 then 1 end) || '/' || count(case when IPISRECORD=0 then 1 end) RECORDSTAT,
     ACCESSAREA
from SDS_DOMAINIP_RECORD a left outer join MID_ABN_SESS_STAT_IDC_D d  on a.ip = d.ip
where a.reportdate = to_date('${yesterday}', 'yyyy/mm/dd') 
group by reportdate,
        company,
        domainname,
        companytype,
        webrecordnum,
        ACCESSAREA
)ta
 left outer join SDS_WEBCRAWLER w on ta.domainname=w.domainname
 left outer join (select domain, sum(nvl(high,0))high, nvl(sum(middle),0) middle, nvl(sum(low),0) low
          from SDS_RISK_STAT_D
         where reporttime = to_date('${yesterday}', 'yyyy/mm/dd')
         group by domain) b
    on ta.domainname = b.domain
left outer join(
 select domainname,sum(urlpv) urlpv from MID_DOMAIN_PV_D c   where REPORTTIME = '${yesterday}'
    group by domainname
) tp on ta.domainname=tp.domainname;
 
COMMIT;

insert into SDS_IDC_SITEMONITOR_M(REPORTTIME,CITYNAME,RECORDNUM,N_RECORDNUM)
select ${monthday},accessarea,sum(case when stage = 1 then 1 else 0 end),sum(case when stage = 0 then 1 else 0 end)
from (select distinct domain, accessarea, stage from sds_idc_sitemonitor where stattime>= ${monthday})
group by accessarea;

COMMIT;

disconnect;
quit;
EOFa


echo  `date` --" sds_idc_sitemonitor_storage end" >>${logfile}


echo  "------------" >>${logfile}
echo  "------------" >>${logfile}
echo  "------------" >>${logfile}

exit 0



